package org.example.quartz.controller;

import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.Session;
import cn.hutool.db.ds.simple.SimpleDataSource;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.example.quartz.BIP.UserQuartz_BIP;
import org.example.quartz.service.UtilService;
import org.example.quartz.service.impl.UtilServiceImpl;
import org.example.quartz.util.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ObjectUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@RestController("qcControllerSlave1")
@RequestMapping(path = "/GSYY/qcController")
public class QcController {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Value(value = "${spring.datasource.databaseType}")
    private String databaseType;
    @Value("${spring.datasource.url}")
    private String jdbcUrl;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value(value = "${spring.datasource.GSYY}")
    private String GSYY;
    @Autowired
    private UserQuartz_BIP userQuartz_BIP;
    @Autowired
    private UtilService utilService;
    @Autowired
    private UtilServiceImpl utilServiceImpl;


    /**
     * 列表查询
     */
    @PostMapping("/getList")
    public Result getList(@RequestBody Map<String, Object> param) {
        StringBuffer condition = new StringBuffer();
        // 账套编码
        if (!ObjectUtils.isEmpty(param.get("yyztcode"))) {
            condition.append(" and YYZTCODE like '%").append(param.get("yyztcode")).append("%'");
        }
        // 年度
        if (!ObjectUtils.isEmpty(param.get("iyear"))) {
            condition.append(" and IYEAR = '").append(param.get("iyear")).append("'");
        }
        // 科目编码
        if (!ObjectUtils.isEmpty(param.get("kmcode"))) {
            condition.append(" and KMCODE like '%").append(param.get("kmcode")).append("%'");
        }

        String countSql = "select count(1) from " + GSYY + ".qc_table where 1 = 1 " + condition;
        Long total = jdbcTemplate.queryForObject(countSql, Long.class);

        if ("oracle".equals(databaseType)) {
            String sql = "select * from " + GSYY + ".QC_TABLE where 1 = 1 " + condition + "order by ID desc OFFSET ((" + Integer.parseInt(param.get("pageNum").toString()) + " - 1 ) *" + Integer.parseInt(param.get("pageSize").toString()) + ") rows fetch next " + Integer.parseInt(param.get("pageSize").toString()) + " rows only";
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
            return Result.success(mapList, total);
        } else if ("dm".equals(databaseType)) {
            String sql = "select * from (select t.*,ROWNUM rn from " + GSYY + ".qc_table t where 1 = 1 " + condition + ") where rn between " + (Integer.parseInt(param.get("pageNum").toString()) - 1) * Integer.parseInt(param.get("pageSize").toString()) + " and " + (Integer.parseInt(param.get("pageNum").toString())) * Integer.parseInt(param.get("pageSize").toString());
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
            return Result.success(mapList, total);
        } else {
            String sql = "select * from " + GSYY + ".qc_table where 1 = 1 " + condition + "limit " + Integer.parseInt(param.get("pageSize").toString()) + " OFFSET " + (Integer.parseInt(param.get("pageNum").toString()) - 1) * Integer.parseInt(param.get("pageSize").toString());
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
            return Result.success(mapList, total);
        }
    }

    /**
     * 批量删除
     */
    @PostMapping("/delete")
    public Result delete(@RequestBody List<String> param) {
        DataSource ds = new SimpleDataSource(jdbcUrl, username, password);
        try (Session session = Session.create(ds)) {
            session.del("qc_table", "id", param);
            return Result.success();
        } catch (Exception e) {
            log.error(e.getMessage());
            return Result.fail(e.getMessage());
        }
    }

    /**
     * 导入
     */
    @PostMapping("/importData")
    public Result importData(@RequestParam MultipartFile file) {
        DataSource ds = new SimpleDataSource(jdbcUrl, username, password);
        try (Session session = Session.create(ds)) {
            Map<String, Object> freemap = new HashMap<>();
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
            List<Map<String, Object>> maps = reader.read(2, 3, Integer.MAX_VALUE);
            for (Map<String, Object> map : maps) {
                Entity entity = Entity.create(utilService.getGSYY() + ".qc_table");
                if ("oracle".equals(databaseType) || "dm".equals(databaseType)) {
                    entity.set("ID", String.valueOf(IdUtil.getSnowflakeNextId()).substring(11));
                }
                for (Map.Entry<String, Object> stringObjectEntry : map.entrySet()) {
                    if (stringObjectEntry.getValue() != null && StrUtil.isNotBlank(stringObjectEntry.getValue().toString()) && "FZ1".equals(stringObjectEntry.getKey())) {
                        if (freemap.containsKey(stringObjectEntry.getValue())) {
                            entity.set("FZGS1", freemap.get(stringObjectEntry.getValue()));
                        } else {
                            // 查库
                            Entity doccode = session.queryOne("SELECT gscode FROM " + utilService.getGSYY() + ".freemap where docname = ?", stringObjectEntry.getValue());
                            if (doccode == null) {
                                return Result.fail("未查询到辅助核算项:" + stringObjectEntry.getValue());
                            }
                            entity.set("FZGS1", doccode.get("gscode"));
                            freemap.put(stringObjectEntry.getValue().toString(), doccode.get("gscode"));
                        }
                        entity.set(stringObjectEntry.getKey(), stringObjectEntry.getValue());
                    } else if (stringObjectEntry.getValue() != null && StrUtil.isNotBlank(stringObjectEntry.getValue().toString()) && "FZ2".equals(stringObjectEntry.getKey())) {
                        if (freemap.containsKey(stringObjectEntry.getValue())) {
                            entity.set("FZGS2", freemap.get(stringObjectEntry.getValue()));
                        } else {
                            // 查库
                            Entity doccode = session.queryOne("SELECT gscode FROM " + utilService.getGSYY() + ".freemap where docname = ?", stringObjectEntry.getValue());
                            if (doccode == null) {
                                return Result.fail("未查询到辅助核算项:" + stringObjectEntry.getValue());
                            }
                            entity.set("FZGS2", doccode.get("gscode"));
                            freemap.put(stringObjectEntry.getValue().toString(), doccode.get("gscode"));
                        }
                        entity.set(stringObjectEntry.getKey(), stringObjectEntry.getValue());
                    } else if (stringObjectEntry.getValue() != null && StrUtil.isNotBlank(stringObjectEntry.getValue().toString()) && "FZ3".equals(stringObjectEntry.getKey())) {
                        if (freemap.containsKey(stringObjectEntry.getValue())) {
                            entity.set("FZGS3", freemap.get(stringObjectEntry.getValue()));
                        } else {
                            // 查库
                            Entity doccode = session.queryOne("SELECT gscode FROM " + utilService.getGSYY() + ".freemap where docname = ?", stringObjectEntry.getValue());
                            if (doccode == null) {
                                return Result.fail("未查询到辅助核算项:" + stringObjectEntry.getValue());
                            }
                            entity.set("FZGS3", doccode.get("gscode"));
                            freemap.put(stringObjectEntry.getValue().toString(), doccode.get("gscode"));
                        }
                        entity.set(stringObjectEntry.getKey(), stringObjectEntry.getValue());
                    } else if (stringObjectEntry.getValue() != null && StrUtil.isNotBlank(stringObjectEntry.getValue().toString()) && "FZ4".equals(stringObjectEntry.getKey())) {
                        if (freemap.containsKey(stringObjectEntry.getValue())) {
                            entity.set("FZGS4", freemap.get(stringObjectEntry.getValue()));
                        } else {
                            // 查库
                            Entity doccode = session.queryOne("SELECT gscode FROM " + utilService.getGSYY() + ".freemap where docname = ?", stringObjectEntry.getValue());
                            if (doccode == null) {
                                return Result.fail("未查询到辅助核算项:" + stringObjectEntry.getValue());
                            }
                            entity.set("FZGS4", doccode.get("gscode"));
                            freemap.put(stringObjectEntry.getValue().toString(), doccode.get("gscode"));
                        }
                        entity.set(stringObjectEntry.getKey(), stringObjectEntry.getValue());
                    } else if (stringObjectEntry.getValue() != null && StrUtil.isNotBlank(stringObjectEntry.getValue().toString()) && "FZ5".equals(stringObjectEntry.getKey())) {
                        if (freemap.containsKey(stringObjectEntry.getValue())) {
                            entity.set("FZGS5", freemap.get(stringObjectEntry.getValue()));
                        } else {
                            // 查库
                            Entity doccode = session.queryOne("SELECT gscode FROM " + utilService.getGSYY() + ".freemap where docname = ?", stringObjectEntry.getValue());
                            if (doccode == null) {
                                return Result.fail("未查询到辅助核算项:" + stringObjectEntry.getValue());
                            }
                            entity.set("FZGS5", doccode.get("gscode"));
                            freemap.put(stringObjectEntry.getValue().toString(), doccode.get("gscode"));
                        }
                        entity.set(stringObjectEntry.getKey(), stringObjectEntry.getValue());
                    } else
                    if (stringObjectEntry.getValue() != null && StrUtil.isNotBlank(stringObjectEntry.getValue().toString()) && "FZ6".equals(stringObjectEntry.getKey())) {
                        if (freemap.containsKey(stringObjectEntry.getValue())) {
                            entity.set("FZGS6", freemap.get(stringObjectEntry.getValue()));
                        } else {
                            // 查库
                            Entity doccode = session.queryOne("SELECT gscode FROM " + utilService.getGSYY() + ".freemap where docname = ?", stringObjectEntry.getValue());
                            if (doccode == null) {
                                return Result.fail("未查询到辅助核算项:" + stringObjectEntry.getValue());
                            }
                            entity.set("FZGS6", doccode.get("gscode"));
                            freemap.put(stringObjectEntry.getValue().toString(), doccode.get("gscode"));
                        }
                        entity.set(stringObjectEntry.getKey(), stringObjectEntry.getValue());
                    } else if (stringObjectEntry.getValue() != null && StrUtil.isNotBlank(stringObjectEntry.getValue().toString()) && "FZ7".equals(stringObjectEntry.getKey())) {
                        if (freemap.containsKey(stringObjectEntry.getValue())) {
                            entity.set("FZGS7", freemap.get(stringObjectEntry.getValue()));
                        } else {
                            // 查库
                            Entity doccode = session.queryOne("SELECT gscode FROM " + utilService.getGSYY() + ".freemap where docname = ?", stringObjectEntry.getValue());
                            if (doccode == null) {
                                return Result.fail("未查询到辅助核算项:" + stringObjectEntry.getValue());
                            }
                            entity.set("FZGS7", doccode.get("gscode"));
                            freemap.put(stringObjectEntry.getValue().toString(), doccode.get("gscode"));
                        }
                        entity.set(stringObjectEntry.getKey(), stringObjectEntry.getValue());
                    } else if (stringObjectEntry.getValue() != null && StrUtil.isNotBlank(stringObjectEntry.getValue().toString()) && "FZ8".equals(stringObjectEntry.getKey())) {
                        if (freemap.containsKey(stringObjectEntry.getValue())) {
                            entity.set("FZGS8", freemap.get(stringObjectEntry.getValue()));
                        } else {
                            // 查库
                            Entity doccode = session.queryOne("SELECT gscode FROM " + utilService.getGSYY() + ".freemap where docname = ?", stringObjectEntry.getValue());
                            if (doccode == null) {
                                return Result.fail("未查询到辅助核算项:" + stringObjectEntry.getValue());
                            }
                            entity.set("FZGS8", doccode.get("gscode"));
                            freemap.put(stringObjectEntry.getValue().toString(), doccode.get("gscode"));
                        }
                        entity.set(stringObjectEntry.getKey(), stringObjectEntry.getValue());
                    }  else {
                        if (stringObjectEntry.getValue() != null && StrUtil.isNotBlank(stringObjectEntry.getValue().toString())) {
                            if ("BEFINBALANCEAMT".equals(stringObjectEntry.getKey()) || "BALANCE".equals(stringObjectEntry.getKey())||"BEGINBALANCEAMT".equals(stringObjectEntry.getKey())) {
                                entity.set(stringObjectEntry.getKey(), stringObjectEntry.getValue().toString().replace(",", ""));
                            } else {
                                entity.set(stringObjectEntry.getKey(), stringObjectEntry.getValue());
                            }
                        }
                    }
                }
                session.insert(entity);
            }
            return Result.success();
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage());
            return Result.fail(e.getMessage());
        }
    }
}
